﻿using System;
using System.Collections.Generic;
using System.Text;
using ScrewTurn.Wiki.PluginFramework;
using System.Diagnostics;
using System.Text.RegularExpressions;
//using MySql.Data.MySqlClient;
using System.Data.SqlClient;


namespace ScrewTurn.Wiki.PluginPack
{
	class NMRExperimentFormatter : NMRExperimentFormatterBase, IFormatterProvider
	{
		#region Version Information
		private const string CurrentVersion = "0.2";
		private const string CurrentRevision = ".2";
		#endregion

		#region Provider Information Property
		private ComponentInformation _info = new ComponentInformation("NMR Experiment Formatter " + CurrentVersion + CurrentRevision, "Vadim Slynko", "");
		public ComponentInformation Information
		{
			get { return _info; }
		}
		#endregion

		#region IFormatterProvider Members
		public bool PerformPhase1
		{
			get { return false; }
		}
		public bool PerformPhase2
		{
			get { return false; }
		}
		public virtual bool PerformPhase3
		{
			get { return true; }
		}

		#endregion

		protected override bool ValidateConfig()
		{
			//Perform a command to make sure connection string for the SQL server is valid.

			SqlConnection myConnection = new SqlConnection(config);
			SqlCommand TestSELECT = myConnection.CreateCommand();

			TestSELECT.CommandText = "SELECT 1 as one";

			try
			{
				myConnection.Open();
				TestSELECT.ExecuteNonQuery();
				return true;
			}
			catch
			{
				host.LogEntry("Wrong Connection string", LogEntryType.Warning, this);
				return false;
			}
			finally
			{
				myConnection.Close();
			}
		}

		public string Format(string raw, ContextInformation context, FormattingPhase phase)
		{
			if (context.Page == null) return raw;
			string result = raw;

			string CurrentPageName = context.Page.Name;
			if (!Regex.Match(CurrentPageName, @"^\w+_id_[0-9]+$").Success) return raw;
			string targetID = CurrentPageName.Substring(CurrentPageName.LastIndexOf('_') + 1, CurrentPageName.Length - CurrentPageName.LastIndexOf('_') - 1);

			SqlConnection myConnection = new SqlConnection(config);
			SqlCommand MainSELECT = myConnection.CreateCommand();
			SqlDataReader MainReader;

			string Template = FormHtml;
			bool IsGeneral = false;

			if (CurrentPageName.Contains("Experiment"))
			{
				MainSELECT.CommandText = GenExpDescrSELECTQuery.Replace("##id##", targetID);
				IsGeneral = true;
			}
			else if (CurrentPageName.Contains("PulseProgram"))
			{
				MainSELECT.CommandText = PpSELECTQuery.Replace("##id##", targetID);
				Template = Template.Replace("##dimentionality##", "").Replace("##type##", "");
				Template = Template.Replace("##display##", "display:none");

			}
			else if (CurrentPageName.Contains("WaveShape"))
			{
				MainSELECT.CommandText = WsSELECTQuery.Replace("##id##", targetID);
				Template = Template.Replace("##dimentionality##", "").Replace("##type##", "");
				Template = Template.Replace("##display##", "display:none");
			}
			else if (CurrentPageName.Contains("CPD"))
			{
				MainSELECT.CommandText = CpdSELECTQuery.Replace("##id##", targetID);
				Template = Template.Replace("##dimentionality##", "").Replace("##type##", "");
				Template = Template.Replace("##display##", "display:none");
			}
			else if (CurrentPageName.Contains("Script"))
			{
				MainSELECT.CommandText = ScrSELECTQuery.Replace("##id##", targetID);
				Template = Template.Replace("##dimentionality##", "").Replace("##type##", "");
				Template = Template.Replace("##display##", "display:none");
			}
			else
			{
				return raw;
			}

			myConnection.Open();
			MainReader = MainSELECT.ExecuteReader();
			while (MainReader.Read())
			{
				result = Template.Replace("##name##", (string)MainReader["Name"]).
								  Replace("##memo##", (string)MainReader["Memo"]).
								  Replace("##authors##", (string)MainReader["Authors"]).
								  Replace("##content##", (string)MainReader["Content"]);
				if (IsGeneral)
				{
					result = result.Replace("##dimentionality##", (string)MainReader["Dim"]);
					result = result.Replace("##type##", (string)MainReader["Type"]);
					result += GenerateButton.Replace("##targetid##", targetID);
				}
			}
			MainReader.Close();

			//Get PulseProgram, WaveShapes, CPDs and Scripts only if it is for Gerneral Exp Description
			if (IsGeneral)
			{
				string input = "";
				MainSELECT.CommandText = FindPulseProgforExpSELECTQuery.Replace("##id##", targetID);
				MainReader = MainSELECT.ExecuteReader();
				while (MainReader.Read())
				{
					result = result.Replace("##pulseprogramname##", (string)MainReader["Name"]);
					result = result.Replace("##pulseprogramid##", MainReader["Id"].ToString());
				}
				MainReader.Close();

				MainSELECT.CommandText = FindWaveShapesforExpSELECTQuery.Replace("##id##", targetID);
				MainReader = MainSELECT.ExecuteReader();
				if (MainReader.HasRows)
				{
					while (MainReader.Read())
					{
						input += "<li><a href=\"WaveShape_id_" + MainReader["Id"].ToString() + ".ashx\">" +
							MainReader["Name"] + "</a></li>";
					}
				}
				else input = "<li>There is no assiciated WaveShapes for this experiment.</li>";
				result = result.Replace("##waveshapelist##", input);
				input = "";
				MainReader.Close();

				MainSELECT.CommandText = FindCPDsforExpSELECTQuery.Replace("##id##", targetID);
				MainReader = MainSELECT.ExecuteReader();
				if (MainReader.HasRows)
				{
					while (MainReader.Read())
					{
						input += "<li><a href=\"CPD_id_" + MainReader["Id"].ToString() + ".ashx\">" +
							MainReader["Name"] + "</a></li>";
					}
				}
				else input = "<li>There is no assiciated Composite Pulse Scheems for this experiment.</li>";
				result = result.Replace("##cpdlist##", input);
				input = "";
				MainReader.Close();

				MainSELECT.CommandText = FindScriptsforExpSELECTQuery.Replace("##id##", targetID);
				MainReader = MainSELECT.ExecuteReader();
				if (MainReader.HasRows)
				{
					while (MainReader.Read())
					{
						input += "<li><a href=\"Script_id_" + MainReader["Id"].ToString() + ".ashx\">" +
							MainReader["Name"] + "</a></li>";
					}
				}
				else input = "<li>There is no assiciated Scripts for this experiment.</li>";
				result = result.Replace("##scriptlist##", input);
				input = "";
				MainReader.Close();
			}
			myConnection.Close();
			return result + raw;
		}

		#region HTML Template for showing database entry
		private const string FormHtml =
			@"
     <p style=""margin: 0px 40px 10px 3px; padding: 5px 2px 5px 5px;
          border-style: groove; border-width: thin; background-color: #DFDFDF;
          ##display##; color: Black;"">
          <i>Dimentionality</i>: <b>##dimentionality##</b>; <i>Type</i>:
          <b>##type##</b>.</p>
     <p style=""margin: 0px 40px 10px 3px; padding: 5px 2px 5px 5px;
          border-style: groove; border-width: thin; background-color: #DFDFDF;
          color: Black;"">
          ##memo##
     </p>
     <p style=""margin: 0px 40px 10px 3px; padding: 5px 2px 5px 5px;
          border-style: groove; border-width: thin; background-color: #DFDFDF;
          color: Black;"">
          <b>Authors:</b> ##authors##</p>
     <div style=""float: right; text-align: left; border-style: groove; border-width: thin; 
padding: 5px 0px 5px 5px; margin: 0px 40px 0px 5px"">
          Content:<br />
          <textarea cols=""60"" rows=""20"" readonly=""readonly"" name=""Content:""
               style=""margin: 0px 0px 0px 0px; text-align: left;"">##content##</textarea>
     
</div>
     <br />
     <div style=""##display##"">
	 <p style=""width: 500px"">
          Pulse Program: <a href=""PulseProgram_id_##pulseprogramid##.ashx"">##pulseprogramname##</a></p>
     <p style=""width: 500px"">
          Wave Shapes:
		<ul>
##waveshapelist##
		</ul>
	 </p>
     <p style=""width: 500px"">
          CPDs:
		<ul>
##cpdlist##
		</ul>
     </p>
     <p style=""width: 500px"">
          Scripts:
<ul>##scriptlist##</ul>
</p>
	 </div>

	<div style=""clear:both"" />
			";

		private const string GenerateButton =
			@"<input type=""button"" name=""ExperimentID"" value=""Generate"" onclick=""window.location.href='Generate.aspx?expid=##targetid##'"" /> 
<br /><br />";
		#endregion

		#region GeneralExperimentDescription SELECT command
		string GenExpDescrSELECTQuery = @"
USE nmrexperiments
SELECT
	e.id AS Id,
	e.name AS Name,
	e.content AS Content,
	e.dim AS Dim,
	e.[type] AS [Type],
	e.[date] AS [Date],
	SUBSTRING(e.memo, 1, 1000) AS Memo,
	dbo.ExpConcatAuthNames(e.id) AS Authors
FROM
	experiments e
WHERE
	e.id=##id##";

		string FindPulseProgforExpSELECTQuery = @"
SELECT
  p.name AS Name,
  p.id AS Id
FROM
  pulseprograms AS p,
  experiments AS e
WHERE
  e.pulseprogram=p.id AND e.id=##id##
";

		string FindWaveShapesforExpSELECTQuery = @"
SELECT
  w.id AS Id,
  w.name AS Name
FROM
  waveshapes AS w,
  experiments AS e,
  relation_waveshapes_to_experiments AS rwe
WHERE
  rwe.wsid=w.id AND rwe.expid=e.id AND e.id=##id##
";
		string FindCPDsforExpSELECTQuery = @"
SELECT
  c.id AS Id,
  c.name AS Name
FROM
  cpds AS c,
  experiments AS e,
  relation_cpds_to_experiments AS rce
WHERE
  rce.cpdid=c.id AND rce.expid=e.id AND e.id=##id##
";
		string FindScriptsforExpSELECTQuery = @"
 SELECT
  s.id AS Id,
  s.name AS Name
FROM
  scripts AS s,
  experiments AS e,
  relation_scripts_to_experiments AS rse
WHERE
  rse.scrid=s.id AND rse.expid=e.id AND e.id=##id##
";

		#endregion

		#region PulseProgram SELECT command
		string PpSELECTQuery = @"
USE nmrexperiments
SELECT
	p.id AS Id,
	p.name AS Name,
	p.content AS Content,
	p.[date] AS [Date],
	SUBSTRING(p.memo, 1, 1000) AS [Memo],
	dbo.PPConcatAuthNames(p.id) AS Authors
FROM
	pulseprograms p
WHERE
	p.id=##id##";
		#endregion

		#region WaveShape SELECT command
		string WsSELECTQuery = @"
USE nmrexperiments
SELECT
	e.id AS Id,
	e.name AS Name,
	e.content AS Content,
	e.[date] AS [Date],
	SUBSTRING(e.memo, 1, 1000) AS Memo,
	dbo.wsConcatAuthNames(e.id) AS Authors
FROM
	waveshapes e
WHERE
	e.id=##id##";
		#endregion

		#region CPD SELECT Command
		string CpdSELECTQuery = @"
USE nmrexperiments
SELECT
	e.id AS Id,
	e.name AS Name,
	e.content AS Content,
	e.[date] AS [Date],
	SUBSTRING(e.memo, 1, 1000) AS Memo,
	dbo.cpdConcatAuthNames(e.id) AS Authors
FROM
	cpds e
WHERE
	e.id=##id##";
		#endregion

		#region Script SELECT Command
		string ScrSELECTQuery = @"
USE nmrexperiments
SELECT
	e.id AS Id,
	e.name AS Name,
	e.content AS Content,
	e.[date] AS [Date],
	SUBSTRING(e.memo, 1, 1000) AS Memo,
	dbo.scrConcatAuthNames(e.id) AS Authors
FROM
	scripts e
WHERE
	e.id=##id##";
		#endregion
	}
}
